package department.fc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import department.fc.domain.Department;
import department.fc.util.DatabaseManager;

public class DepartmentDAO {

	
	private static DepartmentDAO instance = new DepartmentDAO();
	
	private DatabaseManager dbManager = DatabaseManager.getInstance();
	
	private DepartmentDAO(){}
	
	public static DepartmentDAO getInstance(){
		return instance;
	}
	
	
	public int insertDepartment(Department department) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "insert into department values(?,?,?)";
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, department.getDepartmentId());
			pstmt.setString(2, department.getDepartmentName());
			pstmt.setString(3, department.getLocation());
			cnt = pstmt.executeUpdate();
		}finally{
			dbManager.close(conn, pstmt);
		}
		return cnt;
	}
	public int updateDepartmentById(Department department) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "update department set department_name=?, location=? "
							+ "where department_id=?";
		int cnt = 0;
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, department.getDepartmentName());
			pstmt.setString(2, department.getLocation());
			pstmt.setString(3, department.getDepartmentId());
			
			cnt = pstmt.executeUpdate();
			
		}finally{
			dbManager.close(conn, pstmt);
		}
		return cnt;
		
	}
	public int deleteDepartmentById(String departmentId) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		String sql = "delete from department where department_id=?";
		int cnt = 0;
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			
			pstmt.setString(1, departmentId);
			
			cnt = pstmt.executeUpdate();
			
		}finally{
			dbManager.close(conn, pstmt);
		}
		return cnt;
		
	}
	public ArrayList<Department> selectDepartmentList() throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		String sql = "select department_id, department_name, location from department"
				+ " order by department_id";
		ArrayList<Department> list = new ArrayList<Department>();
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while(rset.next()){
				String id = rset.getString(1);
				String name = rset.getString(2);
				String location = rset.getString(3);
				Department dept = new Department(id, name, location);
				list.add(dept);
//			list.add(new Department(rset.getInt(1), rset.getString(2), rset.getString(3)));
			}
			
		}finally{
			dbManager.close(conn, pstmt, rset);
		}
		return list;
	}
	
	public ArrayList<Department> selectDepartementsByLocation(String location) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		String sql = "select department_id, department_name from department where location=? "
				+ " order by department_id";
		ArrayList<Department> list = new ArrayList<Department>();
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, location);
			rset = pstmt.executeQuery();
			while(rset.next()){
				
				list.add(new Department(rset.getString(1), rset.getString(2), location));
			}
			
		}finally{
			dbManager.close(conn, pstmt, rset);
		}
		return list;
	}
	
	public Department selectDepartmentById(String departmentId) throws SQLException{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		Department dept = null;
		String sql = "select department_name, location from department  where department_id=?";
		try{
			conn = dbManager.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, departmentId);
			
			rset = pstmt.executeQuery();
			if(rset.next()){
				dept = new Department(departmentId, rset.getString(1), rset.getString(2));
			}
		}finally{
			dbManager.close(conn, pstmt, rset);
		}
		return dept;
	}
	

	
	
	
}

















